package com.zzty.taskapp.daoImpl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.zzty.taskapp.common.DBUtil;
import com.zzty.taskapp.dao.SelfExamTransationDao;
import com.zzty.taskapp.entity.SelfExamTransation;

public class SelfExamTransationDaoImpl implements SelfExamTransationDao {

	public int searchTotalTime(int examId, int userId) {
		try {
			PreparedStatement pst = DBUtil
					.getPreparedStatement("select TotalTime from selfexamtransation where Examid = ? and Userid = ? ");
			pst.setInt(1, examId);
			pst.setInt(2, userId);
			ResultSet rs = pst.executeQuery();
			int totalTime = 0;
			while (rs.next()) {
				totalTime = rs.getInt("Totaltime");
			}
			// DBUtil.closeConnection();
			return totalTime;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}

	public void updateTotalTime(int examId, int userId, int min) {
		try {
			PreparedStatement pst = DBUtil.getPreparedStatement(
					"update selfexamtransation set Totaltime = ? where Examid = ? and Userid = ?");
			pst.setInt(1, min);
			pst.setInt(2, examId);
			pst.setInt(3, userId);
			pst.executeUpdate();
			DBUtil.closeConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void updateAnswers(int examId, int userId, String answers) {
		try {
			PreparedStatement pst = DBUtil
					.getPreparedStatement("update selfexamtransation set Answers = ? where Examid = ? and Userid = ?");
			pst.setString(1, answers);
			pst.setInt(2, examId);
			pst.setInt(3, userId);
			pst.executeUpdate();
			DBUtil.closeConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void updateIsValid(int examId, int userId, int isValid) {
		try {
			PreparedStatement pst = DBUtil
					.getPreparedStatement("update selfexamtransation set isValid = ? where Examid = ? and Userid = ?");
			pst.setInt(1, isValid);
			pst.setInt(2, examId);
			pst.setInt(3, userId);
			pst.executeUpdate();
			DBUtil.closeConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public String searchStartTime(int examid, int userid) {
		try {
			PreparedStatement pst = DBUtil
					.getPreparedStatement("select Starttime from selfexamtransation where Examid = ? and Userid = ? ");
			pst.setInt(1, examid);
			pst.setInt(2, userid);
			ResultSet rs = pst.executeQuery();
			String starttime = null;
			while (rs.next()) {
				starttime = rs.getString("Starttime");
			}
			// DBUtil.closeConnection();
			return starttime;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public void updateStartTime(int examid, int userid, String starttime) {
		try {
			PreparedStatement pst = DBUtil.getPreparedStatement(
					"update selfexamtransation set Starttime = ? where Examid = ? and Userid = ?");
			pst.setString(1, starttime);
			pst.setInt(2, examid);
			pst.setInt(3, userid);
			pst.executeUpdate();
			// DBUtil.closeConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public List<Integer> searchUseridByExamid(int examid) {
		List<Integer> userids = new ArrayList<Integer>();
		int userid = 0;
		String sql = "select Userid from selfexamtransation where Examid=?";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		try {
			pst.setInt(1, examid);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				userid = rs.getInt("Userid");
				userids.add(userid);
			}
			DBUtil.closeConnection();
			return userids;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return userids;
	}

	public String searchAnswerByExamidAndUserid(int examid, int userid) {
		String sql = "select Answers from selfexamtransation where Examid=? and Userid=?";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		String answer = "";
		try {
			pst.setInt(1, examid);
			pst.setInt(2, userid);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				answer = rs.getString("Answers");

			}
			DBUtil.closeConnection();
			return answer;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return answer;
	}

	public void updateScore(int examid, int userid, String score) {
		String sql = "update selfexamtransation set Score=? where Examid=? and Userid=?";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		try {
			pst.setString(1, score);
			pst.setInt(2, examid);
			pst.setInt(3, userid);
			pst.executeUpdate();
			DBUtil.closeConnection();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void updateComment(int examid, int userid, String comment) {
		String sql = "update selfexamtransation set Comment=? where Examid=? and Userid=?";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		try {
			pst.setString(1, comment);
			pst.setInt(2, examid);
			pst.setInt(3, userid);
			pst.executeUpdate();
			DBUtil.closeConnection();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public int searchIsValid(int examid, int userid) {
		String sql = "select isValid from selfexamtransation where  Examid=? and Userid=?";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		int valid = 0;
		try {
			pst.setInt(1, examid);
			pst.setInt(2, userid);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				valid = rs.getInt("isValid");

			}
			DBUtil.closeConnection();
			return valid;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return valid;
	}

	public String searchScore(int examId, int userId) {
		String score = "";
		String sql = "select Score from selfexamtransation where Examid = ? and Userid = ?";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		try {
			pst.setInt(1, examId);
			pst.setInt(2, userId);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				score = rs.getString("Score");
			}
			return score;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return score;
	}

	public void addSelfExamTransation(SelfExamTransation set) {
		String sql="insert into selfexamtransation(examid,userid) values(?,?)";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		try {
			pst.setInt(1, set.getExamId());
			pst.setInt(2, set.getUserId());
			pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void updateRank(int examId){
		String sql="update selfexamtransation set Rank=1 where Examid=? and Score='A'";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		try {
			pst.setInt(1, examId);
			pst.executeUpdate();
			DBUtil.closeConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public int searchWinnerId(int examId){
		int winnerId = 0;
		String sql="select Userid from selfexamtransation where Examid=? and Score='A'";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		try {
			pst.setInt(1, examId);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				winnerId = rs.getInt("Userid");
			}
			return winnerId;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return winnerId;
	}
	
	public Map<String,String> searchIdAndScore (int examId){
		String sql = "select Userid,Score from selfexamtransation where Examid = ?";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		Map<String,String> map = new HashMap<String,String>();
		try {
			pst.setInt(1, examId);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				map.put(rs.getString("Userid"), rs.getString("Score"));
			}
			return map;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return map;
	}
}
